﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QuanLyThongTinNhanKhau.model.DAO
{
    class TMNhanKhauDAO
    {
        SQLServiceDAO service = new SQLServiceDAO();

        internal System.Data.DataTable loadFindTable(string maNhanKhau)
        {
            DataTable dt = new DataTable();
            if ("All".Equals(maNhanKhau))
            {
                String sql = "select * from NHANKHAU where TinhTrang = 'Enable'";
                dt = service.getDataTable(sql);
            }
            else
            {
                String sql = "select * from NHANKHAU where TinhTrang = 'Enable' and MaNhanKhau = '" + maNhanKhau + "'";
                dt = dt = service.getDataTable(sql);
            }

            return dt;
        }

        internal DataTable btnTimKiem(string maNhanKhau, string tenNhanKhau)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from NHANKHAU where TinhTrang = 'Enable'");

            if (maNhanKhau != "")
            {
                sb.Append(" and MaNhanKhau Like N'%" + maNhanKhau + "%'");
            }

            if (tenNhanKhau != "")
            {
                sb.Append(" and HoTen LIKE N'%" + tenNhanKhau + "%'");
            }

            DataTable dt = service.getDataTable(sb.ToString());
            return dt;
        }

        internal String isValidNhanKhau(string maNhanKhau)
        {
            String sql = "select * from QUANHE where MaNhanKhau = '" + maNhanKhau + "'";
            DataTable dt = service.getDataTable(sql);

            String tmp;

            if (dt.Rows.Count != 0)
            {
                String s = "Select TOP(1) MaHoKhau from QUANHE where MaNhanKhau = '" + maNhanKhau + "'";
                tmp = (string) service.executeScalar(s);
            }else
            {
                tmp = "true";
            }
            return tmp;
        }

        internal String btnLuu(string maHoKhau, string maNhanKhau, string quanHeChuHo)
        {
            String strReturn = "false";
            if ("Chủ Hộ".Equals(quanHeChuHo))
            {
                String sql = "Select TOP(1) * from QUANHE where MaHoKhau = '" + maHoKhau + "'";
                DataTable dt = service.getDataTable(sql);

                if (dt.Rows.Count != 0)
                {
                    strReturn = "DuplicateCH";
                }
                else
                {
                    String a = "insert into QUANHE values ('" + maHoKhau + "','" + maNhanKhau + "',N'" + quanHeChuHo + "')";
                    if (service.executeNonQuery(a))
                    {
                        strReturn = "true";
                    }
                }
            }
            else
            {
                String sql = "insert into QUANHE values ('" + maHoKhau + "','" + maNhanKhau + "',N'" + quanHeChuHo + "')";
                if (service.executeNonQuery(sql))
                {
                    strReturn = "true";
                }
            }

            return strReturn;
        }
    }
}
